<?php
error_reporting(ALL);
set_time_limit(0);
include "otamu_config.php";

define("_VIEWPATH_","views/reporting/");
$layout = "layout";

if (!isset($_GET['act'])) $_GET['act'] = ''; 

class Ahli extends ADOdb_Active_Record
{
	var $_table = "kunci_induk";
}

class Perkhidmatan extends ADOdb_Active_Record
{
	var $_table = "perkhidmatan";
}

$scawangan = 'SELECT id_cawangan, cawangan FROM cawangan ORDER BY cawangan ASC';
if($_REQUEST['cawangan'] !=''){
	$scawangan = "SELECT id_cawangan, cawangan FROM cawangan WHERE id_cawangan='".trim($_REQUEST['cawangan'])."'";
}
$rcawangan = $DB->Execute($scawangan);
$cawangan_arr = array();
while($dc = $rcawangan->FetchNextObject()){
	$cawangan_arr[$dc->ID_CAWANGAN] = $dc->CAWANGAN;
}


switch ($_GET['act']) {
	case "senarai_status_ahli";
		$title = 'Senarai Semua Ahli - Aturan No. Ahli';
		$sql = "SELECT ki.*
					,kh.cawangan
					,pr.keahlian, pr.bangsa, pr.no_tel as telefon
					, kh.telefon as tel_pejabat
					,pr.address1 || ' ' || pr.address2 || ', ' || pr.poskod || ' ' || pr.negeri as alamat
				FROM kunci_induk ki 
			        LEFT JOIN perkhidmatan kh ON kh.no_ahli=ki.no_ahli
					LEFT  JOIN pribadi pr ON pr.no_ahli=ki.no_ahli
					ORDER BY ki.no_ahli ASC
					";
		//echo $sql;exit;
		$res = $DB->Execute($sql);
		include _VIEWPATH_.$act.'.php';
	break;
	
	case "senarai_ahli";
		$condition = '';
		$sql = "SELECT ki.*
				,c.cawangan
				,pr.keahlian, pr.bangsa, pr.no_tel as telefon
				,pr.address1 || ' ' || pr.address2 || ', ' || pr.poskod || ' ' || pr.negeri as alamat
				,kh.telefon as tel_pejabat , kh.no_gaji, j.jawatan
					FROM kunci_induk ki 
			        LEFT JOIN perkhidmatan kh ON kh.no_ahli=ki.no_ahli
					LEFT  JOIN pribadi pr ON pr.no_ahli=ki.no_ahli
					LEFT JOIN jawatan j ON j.kod_jawatan=kh.jawatan_id
					LEFT JOIN cawangan c on c.id_cawangan=kh.cawangan_id
					%s";
		// listing type ...
		$type = (isset($_REQUEST['type'])) ? $_REQUEST['type'] : '';

		$header0  = array('BIL'
						,'NO_AHLI'
						,'NAMA_AHLI'
						,'NOKP_BARU'
						,'NOKP_LAMA'
						,'BANGSA'
						,'KEAHLIAN'
						,'JAWATAN'
						,'TEL_PEJABAT'
						);
		$w0=array(10,15,50,25,25,25,20,80,20);
		
		$header3_no_cwg  = array('BIL'
						,'NO_AHLI'
						,'NAMA_AHLI'
						,'NOKP_BARU'
						,'TELEFON'
						,'KEAHLIAN'
						,'NO_GAJI'
						);
		
		$w3_no_cwg 	=	array(10,15,55,25,23,20);				
		// senarai ahli aktif column in report
		$header1  = array('BIL'
						,'NO_AHLI'
						,'NAMA_AHLI'
						,'NOKP_BARU'
						,'NOKP_LAMA'
						,'BANGSA'
						,'KEAHLIAN'
						,'CAWANGAN'
						,'TELEFON'
						,'TEL_PEJABAT'
						);
		$w1=array(10,15,65,30,25,25,20,50,23,20);
		// ni senarai ahli berenti column in report 
		$header2  = array('BIL'
						,'NO_AHLI'
						,'NAMA_AHLI'
						,'NOKP_BARU'
						,'CAWANGAN'
						,'TELEFON'
						,'TARIKH_BERHENTI'
						,'SEBAB_BERHENTI'
						);
		$w2=array(10,15,65,25,45,25,30);

		$header3  = array('BIL'
						,'NO_AHLI'
						,'NAMA_AHLI'
						,'NOKP_BARU'
						,'ALAMAT'
						,'TELEFON'
						,'KEAHLIAN'
						,'NO_GAJI'
						);
		$w3=array(10,15,65,25,90,25,25);
		
		$condition = '';
		switch($type){
			case 'A': // senarai ahli aktif by nama
				$header = $header1;
				$w = $w1;
				$title = 'Senarai Ahli AKTIF - Aturan Nama';
				$condition = "WHERE pr.keahlian ilike 'AKTIF' ORDER BY ki.nama_ahli ASC";
			break;

			case 'B': // senarai ahli aktif by no ahli
				$header = $header1;
				$w = $w1;
				$title = 'Senarai Ahli AKTIF - Aturan No Ahli';
				$condition = "WHERE pr.keahlian ilike 'AKTIF' ORDER BY ki.no_ahli ASC";
			break;

			case 'C': // senarai ahli aktif by bangsa
				$header = $header1;
				$w = $w1;
				$title = 'Senarai Ahli AKTIF - Aturan Bangsa';
				$condition = "WHERE pr.keahlian ilike 'AKTIF' ORDER BY pr.bangsa ASC";
			break;
			case 'C1': // senarai ahli aktif by bangsa
				$header = $header0;
				//print_r($header);exit;
				$w = $w0;

				$title = 'Senarai Ahli AKTIF - LELAKI';
				$condition = "WHERE pr.keahlian ilike 'AKTIF' AND pr.jantina ilike 'Lelaki' ORDER BY ki.no_ahli ASC";
			break;
			case 'C2': // senarai ahli aktif by bangsa
				$header = $header0;
				$w = $w0;
				$title = 'Senarai Ahli AKTIF - PEREMPUAN';
				$condition = "WHERE pr.keahlian ilike 'AKTIF' AND pr.jantina ilike 'Perempuan' ORDER BY ki.no_ahli ASC";
			break;

			case 'D': // senarai ahli berhenti by nama
				$header = $header2;
				$w = $w2;
				$title = 'Senarai Ahli BERHENTI - Aturan Nama';
				$condition = "WHERE pr.keahlian ilike 'BERHENTI' ORDER BY ki.nama_ahli ASC";
			break;
			
			case 'E': // senarai ahli berhenti by no ahli
				$header = $header2;
				$w = $w2;
				$title = 'Senarai Ahli BERHENTI - Aturan No Ahli';
				$condition = "WHERE pr.keahlian ilike 'BERHENTI' ORDER BY ki.no_ahli ASC";
			break;

			case 'F': // senarai ahli sumbang by NAMA
				$header = $header1;
				$w = $w1;
				$title = 'Senarai Ahli Penyumbang - Aturan Nama';
				$condition = "WHERE ki.ahli_sumbang = 1 ORDER BY ki.nama_ahli ASC";
			break;

			case 'G': // senarai ahli sumbang by NO AHLI
				$header = $header1;
				$w = $w1;
				$title = 'Senarai Ahli Penyumbang - Aturan No Ahli';
				$condition = "WHERE ki.ahli_sumbang = 1 ORDER BY ki.no_ahli ASC";
			break;

			case 'H': // senarai ahli sumbang by NO AHLI
				$header = $header1;
				$w = $w1;
				$title = 'Senarai Ahli Insuran - Aturan Nama';
				$condition = "WHERE ki.ahli_insuran = 1 ORDER BY ki.no_ahli ASC";
			break;
			case 'H1': // senarai ahli sumbang by NO AHLI
				$header = $header0;
				$w = $w0;
				$title = 'Senarai Ahli Insuran - Aturan No Ahli';
				//$condition = "WHERE ki.ahli_insuran = 1 ORDER BY ki.nama_ahli ASC";
				$condition = "WHERE ki.ahli_insuran = 1 ORDER BY ki.no_ahli ASC";
			break;

			case 'I': // senarai ahli  Cawangan
				$header = $header0;
				$w = $w0;
				$title = 'Senarai Ahli Cawangan - '.$cawangan_arr[$_GET['cawangan']];
				//$condition = "WHERE kh.cawangan = '".$_GET['cawangan']."' ORDER BY ki.nama_ahli ASC";
				$condition = "WHERE c.id_cawangan = '".$_GET['cawangan']."' ORDER BY ki.no_ahli ASC";
			break;
			
			case 'J': // senarai ahli by Cawangan : no gaji
				$header = $header3;
				$w = $w3;
				$title = 'Senarai No Gaji Ahli  - '.$cawangan_arr[$_GET['cawangan']];
				$condition = "WHERE c.id_cawangan = '".$_GET['cawangan']."' ORDER BY ki.no_ahli ASC";
			break;
				
			case 'K': // senarai ahli aktif by Cawangan : no gaji
				$header = $header0;
				$w = $w0;
				$title = 'Senarai Ahli Aktif Cawangan - '.$cawangan_arr[$_GET['cawangan']];
				//$condition = "WHERE kh.cawangan = '".$_GET['cawangan']."' AND pr.keahlian ilike 'AKTIF' ORDER BY ki.nama_ahli ASC";
				$condition = "WHERE c.id_cawangan = '".$_GET['cawangan']."' AND pr.keahlian ilike 'AKTIF' ORDER BY ki.no_ahli ASC";
			break;
				
		}
		$SQL = sprintf($sql, $condition);
		//echo("$SQL");exit;
		
		$res = $DB->Execute($SQL);
		//$res_count = $res->RecordCount();
		include _VIEWPATH_.$act.'.php';
	break;
	
	case 'senarai_ahli_by_cawangan':
		//exit;
		$condition = '';
		$sql = "SELECT ki.*
				,c.cawangan
				,pr.keahlian, pr.bangsa, pr.no_tel as telefon
				,pr.address1 || ' ' || pr.address2 || ', ' || pr.poskod || ' ' || pr.negeri as alamat
				,kh.telefon as tel_pejabat 
					FROM kunci_induk ki 
			        LEFT JOIN perkhidmatan kh ON kh.no_ahli=ki.no_ahli
			        LEFT  JOIN pribadi pr ON pr.no_ahli=ki.no_ahli
					INNER JOIN cawangan c ON c.id_cawangan=kh.cawangan_id 
					%s";
		// listing type ...
		$type = (isset($_REQUEST['type'])) ? $_REQUEST['type'] : '';

		// senarai ahli aktif column in report
		$header1  = array('BIL'
						,'NO_AHLI'
						,'NAMA_AHLI'
						,'NOKP_BARU'
						,'BANGSA'
						,'KEAHLIAN'
						//,'CAWANGAN'
						,'ALAMAT'
						,'TEL_PEJABAT'
						);
		//$w1=array(10,15,55,30,25,25,20,50,23);
		$w1=array(10,15,50,25,25,20,100, 20);
		// ni senarai ahli berenti column in report 
		$header2  = array('BIL'
						,'NO_AHLI'
						,'NAMA_AHLI'
						,'NOKP_BARU'
						,'CAWANGAN'
						,'TELEFON'
						,'TARIKH_BERHENTI'
						,'SEBAB_BERHENTI'
						);
		$w2=array(10,15,55,25,45,25,30);
		$w = $w1;
		$header = $header1;
		include _VIEWPATH_.$act.'.php';
	break;
	
	// Laporan Penyata 5E ...
	case 'penyata':
		$header1_0 = array('','','','',4=>'April','Mei','Jun','Julai','Ogos','September');
		$w1_0 = array(10,10,55,35
					,25,25,25,25,25,25);
		$header1  = array('BIL' ,'NO_AHLI' ,'NAMA_AHLI' ,'CAWANGAN'
							,'4:B','4:C','4:D','4:E','4:F'
							,'5:B','5:C','5:D','5:E','5:F'
							,'6:B','6:C','6:D','6:E','6:F'
							,'7:B','7:C','7:D','7:E','7:F'
							,'8:B','8:C','8:D','8:E','8:F'
							,'9:B','9:C','9:D','9:E','9:F'
							);
		$w1=array(10,10,55,35
					,5,5,5,5,5
					,5,5,5,5,5
					,5,5,5,5,5
					,5,5,5,5,5
					,5,5,5,5,5
					,5,5,5,5,5
					);

		$type = $_GET['type'];

		$sql = "SELECT ki.*
				,c.cawangan,pr.keahlian, pr.bangsa, pr.no_tel as telefon, kh.telefon as tel_pejabat 
				FROM kunci_induk ki 
				LEFT JOIN perkhidmatan kh ON kh.no_ahli=ki.no_ahli
				LEFT  JOIN pribadi pr ON pr.no_ahli=ki.no_ahli
				INNER JOIN cawangan c ON c.id_cawangan=kh.cawangan_id 
				%s";

		
		switch($type):
			case 'A': // penyata 5E April -> September
				$header_0 = $header1_0;
				$w_0 = $w1_0;
				$header = $header1;
				$w = $w1;
				$tahun = (isset($_GET['tahun'])) ? $_GET['tahun'] : date("Y");
				$title = "Penyata 5E April - September  #$tahun";
				
				$condition = "WHERE pr.keahlian ilike 'AKTIF' ORDER BY ki.no_ahli";
				$SQL = sprintf($sql, $condition);
				//echo $SQL;exit;
				$res = $DB->Execute($SQL);
				include _VIEWPATH_.$act.'.php';
			break;
			
			case 'B': // pennyata 5E Oct -> March
			
			break;

			case 'C': // pennyata 5E - insuran April -> September
			
			break;
			
			case 'D': // pennyata 5E - insurans Oct -> March
			
			break;
			
			case 'E': // pennyata 5E - Sumbangan April -> September
			
			break;
			
			case 'F': // pennyata 5E - Sumbangan Oct -> March
			
			break;
			
			case 'G': // pennyata Perlu DiBayar
			
			break;
		
		endswitch;
			
	break;
	
	default:
		$act = 'senarai_laporan';
		$sql = "SELECT id_cawangan, cawangan FROM cawangan ORDER BY cawangan ASC";
		$res = $DB->Execute($sql);
		$cawangan_arr = array();
		while($d = $res->FetchNextObject()){
			$cawangan_arr[$d->ID_CAWANGAN] = $d->CAWANGAN;
		}
		include _VIEWPATH_.$layout.'.php';
	break;
}

?>